﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[UDS_WorkAttendence_DaySetting] (
    [datetime] datetime NOT NULL,
    [DayValue] as (dbo.fn_GetDayValue([datetime])) PERSISTED,
    [WeekDay]  int      NULL,
    [NeedDuty] bit      NOT NULL CONSTRAINT [DF_UDS_WorkAttendence_DaySetting_needduty] default((1)),
    [ID]       int      NOT NULL identity(1, 1),

    CONSTRAINT [PK_UDS_WA_DaySetting] PRIMARY KEY NONCLUSTERED ([id] asc)
);
CREATE UNIQUE CLUSTERED INDEX [IX_UDS_WorkAttendence_DaySetting_DayValue] ON dbo.UDS_WorkAttendence_DaySetting([DayValue] asc);
GO

SET NOCOUNT ON

DECLARE @date datetime;
DECLARE @endDate datetime;
DECLARE @weekDay int;

SET DATEFIRST 1; -- Sets the first day of a week as Monday for this session!
SET @date = '2000-01-01';
SET @endDate = '2020-12-31';

WHILE ( @date <= @endDate )
BEGIN
	SET @weekDay = datepart(weekday, @date);
	IF ( @weekDay >= 6 )
	BEGIN
		-- weekends
		INSERT INTO dbo.[UDS_WorkAttendence_DaySetting]([datetime], NeedDuty) VALUES(@date, 0);
	END
	ELSE
	BEGIN
		-- Working days
		INSERT INTO dbo.[UDS_WorkAttendence_DaySetting]([datetime], NeedDuty) VALUES(@date, 1);
	END
	
	SET @date = dateadd(day, 1, @date);
END;
GO